﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class WinSDSSQL
    {
        public static string GetAttachmentList()
        {
            return @"select * from jPOAttachments";
        }
        public static string GetAttachmentByPO()
        {
            return @"select * from jPOAttachments where PoNo = @PoNo";
        }
        public static string GetAllPOList()
        {
            return @"select * from jPOHeader";
        }
        public static string GetAllPOList(string poNum, bool pickup)
        {
            return @"select * from jPOHeader where PoNo like '%" + poNum + "%' and PickedUpFlag = " + (pickup? "1" : "0");
        }
        public static string GetAllPendingPOList()
        {
            return @"select * from jPOHeader where PickedUpFlag = 0";
//            return @"
//select * from jPOHeader where PoNo in
//(
//'AQLA-ES-14-H020',
//'ARES-DS-14-H012',
//'ATNA-DS-14-V007',
//'BATI-CH-14-H008',
//'BTNG-DS-14-H001',
//'DREM-DS-14-V017',
//'LEBN-DS-14-H007',
//'LEBN-ES-14-H001',
//'LEBN-ES-14-H003',
//'LOMB-ES-13-H008',
//'PSNA-CH-14-V003',
//'PSNA-DS-14-V013',
//'PTAR-DS-14-H002',
//'RGNA-CS-14-H001',
//'RGNA-ES-13-H045',
//'SEGI-CH-14-V013'
//)";
        }
        public static string GetAllPendingGRNList()
        {
            return @"select * from jGRNHeader where PickedUpFlag = 0";
        }
        public static string GetAllPODetailByPONumber()
        {
            return @"select * from JPODetails where PoNo = @PoNo order by jPODetailsID";
        }
        public static string GetAllGRNDetailByPONumber()
        {
            return @"select * from JPODetails where PoNo = @PoNo";
        }

        public static string GetAllSupplierAgresso()
        {
            return @"select * from c_supplier where pickedupflag = 0";//31072013
        }

        public static string UpdatePickupFlagSupplierAgresso()
        {
            return @"update c_supplier set pickedupflag = @pickedupflag, PickedUpDate = @PickedUpDate where apar_id = @apar_id";//31072013
        }

        public static string SelectCountJVendorByVendorID()
        {
            return @"select count(*) from jVendors where VendorId = @VendorId";
        }

        public static string InsertJVendors()
        {
            return @"
INSERT INTO [jVendors]
           ([Client]
           ,[VendorId]
           ,[VendorName]
           ,[CurrencyCode]
           ,[Country]
           ,[VendorAddress1]
           ,[VendorAddress2]
           ,[VendorPhone]
           ,[VendorFax]
           ,[VendorEmailID]
           ,[PaymentTerms]
           ,[PaymentMethod]
           ,[EnabledFlag]
           ,[AddedOn]
           ,[PickedUpFlag]
           ,[ShortName]
           ,[Description]
           ,[ResultDetails]
           ,[ExecutionID])
     VALUES
           (@Client
           ,@VendorId
           ,@VendorName
           ,@CurrencyCode
           ,@Country
           ,@VendorAddress1
           ,@VendorAddress2
           ,@VendorPhone
           ,@VendorFax
           ,@VendorEmailID
           ,@PaymentTerms
           ,@PaymentMethod
           ,@EnabledFlag
           ,@AddedOn
           ,@PickedUpFlag          
           ,@ShortName
           ,@Description
           ,@ResultDetails
           ,@ExecutionID)
";
        }

        public static string UpdatePOPickupFlag()
        {
            return @"update jPOHeader set PickedUpFlag=@PickedUpFlag,PickedUpAt=@PickedUpAt,AgressoRowId=@AgressoRowId where PoNo=@PoNo";
        }

        public static string GetCountGRNByArrivedID()
        {
            return "select count(*) from jGRNHeader where ArrivalID=@ArrivalID";
        }

        public static string InsertJGRNDetails()
        {
            return @"
            INSERT INTO jGRNDetails
           ([VslCode]
           ,[PoNo]
           ,[ArrivalID]
           ,[PoLineItemNo]
           ,[QtyReceived]
           ,[LineItemComment]
           ,[AddedOn]
           ,[PickedUpFlag]
           ,[AgressoRowId])
     VALUES
           (@VslCode
           ,@PoNo
           ,@ArrivalID
           ,@PoLineItemNo
           ,@QtyReceived
           ,@LineItemComment
           ,@AddedOn
           ,@PickedUpFlag
           ,@AgressoRowId)
            ";
        }

        public static string InsertJGRNHeader()
        {
            return @"INSERT INTO jGRNHeader
           ([VslCode]
           ,[PoNo]
           ,[ArrivalID]
           ,[Remark]
           ,[ReceivedDate]
           ,[ReceivedBy]
           ,[Place]
           ,[GRNComments]
           ,[AddedOn]
           ,[PickedUpFlag]
           ,[ResultDetails]
           ,[ExecutionID])
     VALUES
           (@VslCode
           ,@PoNo
           ,@ArrivalID
           ,@Remark
           ,@ReceivedDate
           ,@ReceivedBy
           ,@Place
           ,@GRNComments
           ,@AddedOn
           ,@PickedUpFlag
           ,@ResultDetails
           ,@ExecutionID)";
        }

        public static string GetPOInterfaceByID()
        {
            return "select * from i_jPOHeader where jPOheaderID=@jPOheaderID";
        }

        public static string InsertPOInterface()
        {
            return @"
insert into i_jPOHeader 
(
jPOheaderID,PickedUpFlag,PickedUpAt,remarks,PoNo,PoDate
           ,[Client]
           ,[LegalEntity]
           ,[VslCode]
           ,[Category]
           ,[WorkOrderID]
           ,[VendorID]
           ,[CurrencyCode]
           ,[CurrencyXrate]
           ,[PaymentTerms]
           ,[PaymentMethod]
           ,[DeliveryMethod]
           ,[DeliveryTerms]
           ,[DeliveryLocation]
           ,[POComments]
           ,[Responsible]
           ,[Responsible2]
           ,[DiscountAmount]
           ,[Status]
           ,[Voucher_Type]
           ,[AddedOn]
           ,[AgressoRowId]
           ,[TotalLineItems]
           ,[ExecutionID]
           ,[AgressoPoID]
           ,[AgressoPODate]
           ,[AgressoApvdDate]
) 
            values 
(
@jPOheaderID,@PickedUpFlag,@PickedUpAt,@remarks,@PoNo,@PoDate
,@Client
           ,@LegalEntity
           ,@VslCode
           ,@Category
           ,@WorkOrderID
           ,@VendorID
           ,@CurrencyCode
           ,@CurrencyXrate
           ,@PaymentTerms
           ,@PaymentMethod
           ,@DeliveryMethod
           ,@DeliveryTerms
           ,@DeliveryLocation
           ,@POComments
           ,@Responsible
           ,@Responsible2
           ,@DiscountAmount
           ,@Status
           ,@Voucher_Type
           ,@AddedOn
           ,@AgressoRowId
           ,@TotalLineItems
           ,@ExecutionID
           ,@AgressoPoID
           ,@AgressoPODate
           ,@AgressoApvdDate
)";
        }

        public static string UpdatePOInterface()
        {
            return "update i_jPOHeader set PickedUpFlag=@PickedUpFlag,PickedUpAt=@PickedUpAt,remarks=@remarks, PoNo=@PoNo,PoDate=@PoDate where jPOheaderID=@jPOheaderID";
        }

        public static string GetAllPendingWinSDSPO(string p)
        {
            return string.Format(@"select * from i_jPOHeader where PickedUpFlag='0'
            and PickedUpFlag +  replace(convert(char(11), pickedupat, 106),' ','-')+remarks+PoNo+ replace(convert(char(11), poDate, 106),' ','-') 
            like '%{0}%' order by PoDate", p);
        }

        public static string GetAllPendingWinSDSPO()
        {
            return "select * from i_jPOHeader where PickedUpFlag='0' order by PoDate";
        }
        public static string GetAllPendingWinSDSPOCount()
        {
            return "select count(*) from i_jPOHeader where PickedUpFlag='0'";
        }

        public static string GetAllNullPOId()
        {
            return "select * from jPOHeader where pickedupflag in (1) and AgressoPOID is null"; 
        }

        public static string GetAllNullPOIdMiddleLayer()
        {
            return "select distinct PoNo, Client, AgressoPOID, null as Agressoapvddate from i_jPOHeader where pickedupflag in (1) and AgressoPOID = 0 and client is not null";
        }

        public static string GetAllNotNullPOId()
        {
            return @"select * from jPOHeader where pickedupflag in (1) and PO_Remark is not null and AgressoPOID is not null order by 1 desc";
        }

        public static string GetApoHeaderByPOnumber()
        {
            return "select *  from apoheader where client = @client and ext_ord_ref = @ext_ord_ref ";
        }

        public static string GetjPOHeaderByPOnumber()
        {
            return "select *  from jPOHeader where client = @client and PoNo = @PoNo ";
        }

        public static string UpdatePOID()
        {
            return "update jPOHeader set AgressoPOID = @AgressoPOID  where pono = @pono and client =@client";
        }
        public static string UpdatePOIDMiddleLayer()
        {
            return "update i_jPOHeader set AgressoPOID = @AgressoPOID, AgressoApvdDate=@AgressoApvdDate  where pono = @pono and client =@client";
        }
        public static string GetAllNullApproveDatePO()
        {
            return "select * from jPOHeader where PickedUpFlag in (1) And AgressoApvdDate Is Null And AgressoPoID is not Null";
        }

        public static string GetApprovedDateByPOnumber()
        {
            return @"select client, col2_value as order_id,convert(varchar(23),max(action_date),121) as apr_date from awftaskfin 
                    where action_code = 'AP' and wf_status = 'FIN' and client = @client and col2_value = @apoid 
                    group by client, col2_value";
        }

        public static string UpdateApprovedDatePO()
        {
            return "update jPOHeader set Agressoapvddate = @Agressoapvddate  where agressopoid = @agressopoid and client =@client";
        }

        public static string InsertWinSDSAccountMapping()
        {
            return @"INSERT INTO i_winsds_itemcode_account_mapping
           ([itemCode]
           ,[account]
           ,[dim_2]
           ,[created_date]
           ,[modified_date])
     VALUES
           (@itemCode
           ,@account
           ,@dim_2
           ,@created_date
           ,@modified_date)";
        }
        public static string UpdateWinSDSAccountMapping()
        {
            return @"update i_winsds_itemcode_account_mapping set 
           [itemCode] = @itemCode 
           ,[account] = @account  
           ,[dim_2] = @dim_2 
           ,[modified_date] = @modified_date 
            where id = @id";
        }

        public static string DeleteWinSDSAccountMapping()
        {
            return @"delete from i_winsds_itemcode_account_mapping where id = @id";
        }

        public static string FindByItemCodeWinSDSAccountMapping()
        {
            return @"select * from i_winsds_itemcode_account_mapping where itemCode = @itemCode";
        }

        public static string FindAllWinSDSAccountMapping()
        {
            return @"select * from i_winsds_itemcode_account_mapping";
        }

        public static string InsertPODetailInterface()
        {
            return @"
INSERT INTO i_jPODetails
           ([jPODetailsID]
           ,[PoNo]
           ,[PoLineItemNo]
           ,[ItemCode]
           ,[ItemDetail]
           ,[UOM]
           ,[Packing]
           ,[OrdQty]
           ,[UnitPrice]
           ,[ReadinessDate]
           ,[AccountCode]
           ,[ItemRemarks]
           ,[AddedOn]
           ,[insert_date])
     VALUES
           (@jPODetailsID
           ,@PoNo
           ,@PoLineItemNo
           ,@ItemCode
           ,@ItemDetail
           ,@UOM
           ,@Packing
           ,@OrdQty
           ,@UnitPrice
           ,@ReadinessDate
           ,@AccountCode
           ,@ItemRemarks
           ,@AddedOn
           ,@insert_date)
";
        }
    }
}
